package com.kexun.dao.impl;

import com.kexun.dao.AdmDao;
import com.kexun.pojo.*;
import com.kexun.util.DruidUtil;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class AdmDaoImpl implements AdmDao {
    JdbcTemplate t = new JdbcTemplate(DruidUtil.getDataSource());


    /**
     * ajax实现填充数据
     *
     * @param userNum
     * @return
     */
    @Override
    public User findInfo(String userNum) {

        String sql ="select * from user where user_num = '"+userNum+"'";


        User user = null;
        try {
            user = t.queryForObject(sql, new BeanPropertyRowMapper<>(User.class));

        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        return user;

    }

    /**
     * 修改用户信息
     *
     * @param user
     * @return
     */
    @Override
    public int updateAdm(User user) {

        String userNum = user.getUserNum();
        String userName = user.getUserName();

        String password = user.getPassword();
        String phone = user.getPhone();
        Integer roleId = user.getRoleId();

        String sql ="update user set user_name='"+userName+"',password='"+password+"',phone='"+phone+"',role_id='"+roleId+"' where user_num ='"+userNum+"'";
        int i = t.update(sql);


        return i;


    }

    /**
     * 课程信息查看
     *
     * @return
     */
    @Override
    public Map<String, Object> finAllCos(String limitStr ,String pageStr) {
        int limit = Integer.parseInt(limitStr);
        int page = Integer.parseInt(pageStr);

        String sql ="select * from course limit ?,?";
       String sqlCount = "select count(*) from course";

        List<Course> list = null;
        int i = 0;
        try {
           list = t.query(sql, new BeanPropertyRowMapper<>(Course.class), (page - 1) * limit, limit);
           i = t.queryForObject(sqlCount,Integer.class);

        } catch (DataAccessException e) {
            e.printStackTrace();
        }


        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);

        return  map;



    }

    /**
     * 修改课程信息
     *
     * @param course
     * @return
     */
    @Override
    public int editCos(Course course) {

        String sql ="update course set course_credit='"+course.getCourseCredit()+"',course_hours='"+course.getCourseHours()+"',course_teacher='"+course.getCourseTeacher()+"',coursedate= '"+course.getCourseDate()+"'where course_name='"+course.getCourseName()+"'";

        int i = t.update(sql);

        return i;


    }

    /**
     * 添加课程信息
     *
     * @param course
     * @return
     */
    @Override
    public int addCourse(Course course) {

        String sql ="insert into course values(default,'"+course.getCourseName()+"','"+course.getCourseCredit()+"','"+course.getCourseHours()+"','"+course.getCourseTeacher()+"','"+course.getCourseDate()+"')";

        System.out.println(sql);
        int i = t.update(sql);
        return i;


    }

    /**
     * 删除课程
     *
     * @param couName
     * @return
     */
    @Override
    public int delCouse(String couName) {
        String sql ="delete from course where course_name = '"+couName+"'";
        int i = t.update(sql);
        return i;

    }

    @Override
    public List<Course> finAllCourse() {
        String sql = "select * from course";
        List<Course> list = null;
        try {
             list= t.query(sql, new BeanPropertyRowMapper<>(Course.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        return list;

    }

    /**
     * 查找一个课程的信息
     *
     * @param userNum
     * @return
     */
    @Override
    public Map<String, Object> finOneCourse(String userNum) {
        String sql ="select * from course where ";


    return null;
    }

    @Override
    public Map<String, Object> finScoreAll(int limit, int page) {

        String sql1= "select * from score limit ?,?";

        String sqlCount = "select count(*) from score";
        List<Score> list = null;
        int i = 0;
        try {
            list = t.query(sql1, new BeanPropertyRowMapper<>(Score.class), (page - 1) * limit, limit);
            i = t.queryForObject(sqlCount,Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);


        return map;



    }

    @Override
    public Map<String, Object> findTeaInfoAll(int limit, int page) {
        String sql1= "select * from teacher limit ?,?";

        String sqlCount = "select count(*) from teacher";
        List<Teacher> list = null;
        int i = 0;
        try {
            list = t.query(sql1, new BeanPropertyRowMapper<>(Teacher.class), (page - 1) * limit, limit);
            i = t.queryForObject(sqlCount,Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);

        return map;
    }

    @Override
    public Teacher findTeacher(String teaNum) {

        String sql ="select * from teacher where tea_num='"+teaNum+"'";
        Teacher teacher = null;
        try {
        teacher = t.queryForObject(sql, new BeanPropertyRowMapper<>(Teacher.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        System.out.println(teaNum);
        System.out.println(teacher);
        return teacher;

    }

    @Override
    public int editTeacher(Teacher teacher) {

        String sql = "update teacher set tea_name='"+teacher.getTeaName()+"',tea_sex='"+teacher.getTeaSex()+"',tea_age='"+teacher.getTeaAge()+"',tea_course='"+teacher.getTeaCourse()+"',major='"+teacher.getMajor()+"',department='"+teacher.getDepartment()+"'where tea_num='"+teacher.getTeaNum()+"'";


        int i = t.update(sql);
        return i;


    }

    @Override
    public int delTea(String teaNum) {

        String sql ="delete from teacher where tea_num='"+teaNum+"'";

        int i = t.update(sql);

        return i;

    }

    @Override
    public int addTeacher(User user, Teacher teacher) {

        String sql = "insert into teacher values(default,'"+teacher.getTeaNum()+"','"+teacher.getTeaName()+"','"+teacher.getTeaSex()+"','"+teacher.getTeaAge()+"','"+teacher.getTeaCourse()+"','"+teacher.getMajor()+"','"+teacher.getDepartment()+"') ";
        String sql1 = "insert into user values(default,'"+user.getUserNum()+"','"+user.getUserName()+"','"+user.getPassword()+"','"+user.getPhone()+"','"+user.getRoleId()+"')";


        int i = t.update(sql);
        int i2 = t.update(sql1);

        if(i>0 && i2 >0){

        return i2;
        }else{
            return 0;
        }
    }

    @Override
    public Course findCouse(String couName) {


     String sql ="select * from course where course_name='"+couName+"'";

        Course course = null;
        try {
             course = t.queryForObject(sql, new BeanPropertyRowMapper<>(Course.class));
        } catch (DataAccessException e) {
            return null;
        }


        return course;

    }

    @Override
    public Score findScore(String stuNum,String courseName) {

        String sql ="select * from score where stu_num =? and course_name=?";

        Score score = null;
        try {
            score = t.queryForObject(sql,new BeanPropertyRowMapper<>(Score.class),stuNum,courseName);
        } catch (DataAccessException e) {
           return null;
        }
        System.out.println(score);
        return score;
    }

    @Override
    public int editSco(Score score) {

        String sql ="update score set stu_class='"+score.getStuClass()+"',course_name='"+score.getCourseName()+"',score_grade='"+score.getScoreGrade()+"',major='"+score.getMajor()+"' where stu_num='"+score.getStuNum()+"' and course_name='"+score.getCourseName()+"'";


        int i = t.update(sql);

        return i;


    }

    @Override
    public int delSco(String stuNum) {
        System.out.println(22);
        String sql ="delete from score where stu_num='"+stuNum+"'";
        System.out.println(sql);
        int i = t.update(sql);
        System.out.println(i);
        return i;


    }

    @Override
    public int addScore(Score score) {
        String sql ="insert into score values(default,'"+score.getStuNum()+"','"+score.getStuName()+"','"+score.getStuClass()+"','"+score.getCourseName()+"','"+score.getScoreGrade()+"','"+score.getMajor()+"')";
        int i = t.update(sql);
        return i;
    }

    @Override
    public Map<String, Object> allStuSco(String limit, String page) {

        System.out.println(22);
        int lim = Integer.parseInt(limit);
        int pag = Integer.parseInt(page);
        String sql ="select stu_num,stu_name,stu_class,major,sum(score_grade) sumScore, avg(score_grade) Avg from score group by stu_num limit ?,?";
        String sqlCount ="select count(distinct stu_num) from score";

        System.out.println(sql);
        System.out.println(sqlCount);

        List<AllStu> list = null;
        int i = 0;
        try {
             list = t.query(sql,new BeanPropertyRowMapper<>(AllStu.class), (pag - 1) * lim, lim);
             i = t.queryForObject(sqlCount,Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);
        // map 打印结果
        //{msg=, code=0, data=[AllStu{stuNum='12341', stuName='阿贾克斯', stuClass='生化班', major='化工', sumScore=297.0, Avg=99.0}, AllStu{stuNum='14343', stuName='钢铁侠', stuClass='复联', major='机器智能', sumScore=99.0, Avg=99.0}, AllStu{stuNum='160341', stuName='王五', stuClass='信管1601', major='信息管理与信息系统', sumScore=417.6, Avg=83.52}, AllStu{stuNum='160343', stuName='闫玉平', stuClass='信管1601', major='信息管理与信息系统', sumScore=287.0, Avg=71.75},
        // AllStu{stuNum='160345', stuName='周梦琪', stuClass='信管1601', major='信息管理与信息系统', sumScore=325.0, Avg=81.25}], count=22}
        System.out.println(map);
        return map;


    }

    @Override
    public Map<String, Object> findScoreLike(String courseName, String stuNum, String stuName, String stuClass, String major, String limitStr, String pageStr) {

        int limit = Integer.parseInt(limitStr);
        int page = Integer.parseInt(pageStr);

        String sql = "select * from score where course_name like '%"+courseName+"%' and stu_num like '%"+stuNum+"%' and stu_name like '%"+stuName+"%' and stu_class like '%"+stuClass+"%' and major like '%"+major+"%' limit ?,? ";
        String sqlCount = "select count(*) from score where course_name like '%"+courseName+"%' and stu_num like '%"+stuNum+"%' and stu_name like '%"+stuName+"%' and stu_class like '%"+stuClass+"%' and major like '%"+major+"%'";
        String sqlCount1  = "select count(*) from score where course_name like %'"+courseName+"'% and stu_num like %'"+stuNum+"'% and stu_name like %'"+stuName+"'% and stu_class like %'"+stuClass+"'% and  major like %'"+major+"'% ";
        System.out.println(sql);
        System.out.println(sqlCount);

//        String sql1 ="select * from score where stu_name like '%"+stuName+"%' and stu_class like '%"+stuClass+"%'and cast(stu_num as char)   like '%"+stuNum+"%' and course_name = (select tea_course from teacher where tea_num = '"+teaNum+"') limit ?,?";





        List<Score> list = null;
        int i = 0;

        try {
             list= t.query(sql, new BeanPropertyRowMapper<>(Score.class), (page - 1) * limit, limit);
             i= t.queryForObject(sqlCount,Integer.class);

        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);

        System.out.println(map);
        return map;
    }

    @Override
    public Map<String, Object> findStudentAll(String limitStr, String pageStr) {
        int limit = Integer.parseInt(limitStr);
        int page = Integer.parseInt(pageStr);
        String sql = "select * from student limit ?,?";
        String sqlCount = "select count(*) from student";
        List<Student> list = null;
        int i = 0;
        try {
         list= t.query(sql, new BeanPropertyRowMapper<>(Student.class), (page - 1) * limit, limit);
         i = t.queryForObject(sqlCount,Integer.class);

        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);

        return map;

    }

    @Override
    public Student finStudent(String stuNum, String stuName, String stuClass) {
        String sql ="select * from student where stu_num='"+stuNum+"' and stu_name ='"+stuName+"' and stu_Class='"+stuClass+"'";
        Student student = null;
        try {
             student = t.queryForObject(sql, new BeanPropertyRowMapper<>(Student.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        return student;

    }

    @Override
    public int editStu(Student student) {
        System.out.println(22);
        String sql = "update student set stu_sex='"+student.getStuSex()+"',stu_age='"+student.getStuAge()+"',stu_class='"+student.getStuClass()+"',major='"+student.getMajor()+"',department='"+student.getDepartment()+"' where stu_num = '"+student.getStuNum()+"'";
        System.out.println(sql);

        int i = t.update(sql);
        System.out.println(i);
        return i ;
    }

    @Override
    public int delStu(String stuNum, String stuName, String stuClass) {
        String sql ="delete from student where stu_num ='"+stuNum+"' and stu_name= '"+stuName+"' and stu_class='"+stuClass+"'";
        int i = t.update(sql);
        return i;


    }

    @Override
    public int addStudent(User user, Student student) {
        String sql ="insert into user values(default,'"+user.getUserNum()+"','"+user.getUserName()+"','"+user.getPassword()+"','"+user.getPhone()+"','"+user.getRoleId()+"')";

        String sql1 ="insert into student values(default,'"+student.getStuNum()+"','"+student.getStuName()+"','"+student.getStuSex()+"','"+student.getStuAge()+"','"+student.getStuClass()+"','"+student.getMajor()+"','"+student.getDepartment()+"')";

        int i = t.update(sql);
        int i1 = t.update(sql1);
        if( i>0 && i1>0 ){
            return i1;
        }else{
            return  0;
        }


    }

    @Override
    public Map<String, Object> findUsrAll(String limitStr, String pageStr) {
        int limit = Integer.parseInt(limitStr);
        int page = Integer.parseInt(pageStr);

        String sql ="select user_num,user_name,password,phone, case  when role_id = 0 then \"学生\" when role_id = 1 then \"老师\" when role_id =2 then \"管理员\" end role_id  from user limit ?,?";
        String sql1 ="select user_num,user_name,password,phone, case  when role_id = 0 then '学生' when role_id = 1 then '老师' when role_id =2 then '管理员' end role_id from user limit ?,?";
        String sqlCount ="select count(*) from user";

        List<Usert> list = null;
        int i = 0;
        try {
            list= t.query(sql1, new BeanPropertyRowMapper<>(Usert.class), (page - 1) * limit, limit);
            i = t.queryForObject(sqlCount,Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);
        System.out.println(map);
        return map;
    }

    @Override
    public Usert findUser(String userNum) {
        String sql ="select user_num,user_name,password,phone,case role_id when 0 then '学生' when 1 then '老师' when 2 then '管理员' end role_id from user where user_num='"+userNum+"'";
        Usert usert = null;
        try {
             usert = t.queryForObject(sql, new BeanPropertyRowMapper<>(Usert.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        return usert;

    }

    @Override
    public int editUsr(User user) {
        String sql ="update user set user_name ='"+user.getUserName()+"',password='"+user.getPassword()+"',phone='"+user.getPhone()+"',role_id='"+user.getRoleId()+"' where user_num='"+user.getUserNum()+"'";
        int i = t.update(sql);
        return i;


    }

    @Override
    public int addUser(Usert user) {
        String sql ="insert into user values(default,'"+user.getUserNum()+"','"+user.getUserName()+"','"+user.getPassword()+"','"+user.getPhone()+"','"+user.getRoleId()+"')";
        int i = t.update(sql);
        return i;


    }


}
